import pandas as pd
from sqlalchemy import create_engine
import re
import matplotlib.pyplot as plt
import numpy as np
from collections import Counter
import wordcloud
#数据库连接参数
db_info = {'user': 'root',
           'password': '12345678',
           'host': '127.0.0.1',
           'database': 'test_db'  }  # 这里我们事先指定了数据库，后续操作只需要表即可
#创建MySQL数据库链接
engine = create_engine('mysql+pymysql://%(user)s:%(password)s'
                       '@%(host)s/%(database)s?charset=utf8'%db_info,encoding='utf-8')
#获取所有不重复的岗位描述
data = pd.read_sql("select distinct  job from qcwy  ",
con = engine)
desc_list=np.array(data).tolist() #转为list
print(desc_list)
result=[]
words=[]
#循环遍历，根据正则表达式提取英文字符，并将所有英文插入到result列表
for desc in desc_list:  #
    # word_list = re.findall("[a-zA-Z]{1,}", desc[0])
    result.extend(desc)
#排除列表
print(result)
excludes=["db","it","web","db","io","j","k","boot","excel","b","pc","tb"]
#排除不关注的关键字
for w in result:
    if  w.lower() not in excludes :
        words.append(w.lower())
print(words)
wd = Counter(words) #词频统计
#实例化词云
wc = wordcloud.WordCloud(
    font_path='/System/Library/Fonts/Hiragino Sans GB.ttc', # 设置字体格式
    background_color="white",
    scale=2, #放大2倍，否则不清晰
    width=640,  #宽度
    height=480, #高度
    max_words=80, # 最多显示词数
    max_font_size=100 # 字体最大值

)
wc.generate_from_frequencies(wd) # 词频统计结果生成词云
plt.imshow(wc) # 显示词云
plt.axis('off') # 关闭坐标轴
plt.show() # 显示图像